<?php
namespace Common\ORG;

/**
 * Excel 文件解析与下载服务接口
 *
 */
class Excel {
    protected $m_res = array('rcode'=>0,'data'=>NULL,'msg'=>'500 Internal Error');
    protected $m_sheet_error = "";
    protected $m_sheet_data = array();
    protected $m_sheet_active = false;

    //获取上传根目录
    private function _root()
    {
        $root = __ROOT__ . DIRECTORY_SEPARATOR;
        $www_root = realpath(  $root ).DIRECTORY_SEPARATOR;
        $cfg_upload = $this->config();
        $www_root = $cfg_upload&&isset( $cfg_upload['DIR'] ) ? $www_root.$cfg_upload['DIR']:'uploads';
        $www_root = str_replace('//', '/', $www_root.DIRECTORY_SEPARATOR );
        if( !is_dir( $www_root )){
            mkdir( $www_root, 0755, true );
        }
        return $www_root;
    }

    /**
     * Excel Sheet表头最多解析100行
     * 
    */
    private function _maxRow( $ExcelSheet)
    {
        $row = FALSE;
        if( $ExcelSheet && $ExcelSheet instanceof \PHPExcel_Worksheet){
            $row = 0;
            $allColumn = $ExcelSheet->getHighestDataColumn();
            $countClomun = $this->_index( $allColumn );
            $max = 209999;//set max row to parse
            $irow = 0; ##TODO empty row
            for($i=1;$i<$max; $i++ ){
                $v = $ExcelSheet->getCell( 'A'.$i )->getValue();
                if( $v ){
                    $row ++;
                }
                else{
                    $row_clear = 0;
                    for ($iclear=1;$iclear<=$countClomun && $countClomun<=100;$iclear++) {
                        $index_cell = $this->_index($iclear);
                        $v = $ExcelSheet->getCell( $index_cell.$i )->getValue();
                        if ($v)
                            $row_clear++;
                    }
                    if ( $row_clear==0 ){
                        break;
                    }
                    else
                        $row++;
                }
            }

        }
        return $row;
    }

    /**
     * 从文件中解析EXcel数据
     * @param  string $path  文件地址信息：访问绝对路径或者相对路径
     * @return array       array(
     *                            'rcode' => 0/1,
     *                            'msg'   => '错误提示消息',
     *                            'data' = > array(
     *                                        'sheets' =>  integer Excel文件中sheet数量
     *                                        'column' => array(), 以's' + 表格排序 来计算数据
     *                                        'row'    => array()
     *                                    )
     *                         )
     */
    public function parse_file( $path )
    {
        $res = $this->m_res;
        $MAtt = new AttUpload();
        $root = $MAtt->_root( false );
        if( FALSE === strpos($path , $root ) ){
            $path = substr( $root,0,-1).$path;
        }
        if( ! $this->valid( $path ) )
            $res['msg'] = "无效的Excel文件";
        else{
            Vendor('PHPExcel.PHPExcel');
            $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;;
            $cacheSettings = array('memoryCacheSize'=>'32MB');
            \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
            $PHPExcel = new \PHPExcel();
            $excelType = \PHPExcel_IOFactory::identify($path);
            $PHPReader = \PHPExcel_IOFactory::createReader($excelType);
            //增加readDataOnly 之后Excel时间判断错误
            //$PHPReader->setReadDataOnly(true);
            $PHPExcel = $PHPReader->load( $path );
            $data = array('column'=>array(), 'row'=> array(),'sheets'=>0);
            $data['sheets'] =  $PHPExcel->getSheetCount();
            try{
                for($sheeIndex =0 ; $sheeIndex < $data['sheets']; $sheeIndex ++ ){
                    $ic = 0;
                    $PHPExcel->setActiveSheetIndex( $sheeIndex );
                    $currentSheet = $PHPExcel->getActiveSheet();
                    $allColumn = $currentSheet->getHighestDataColumn();
                    $allRow = $this->_maxRow( $currentSheet ) - 1;//将表头计算在外
                    $countClomun = $this->_index( $allColumn );
                    $data['column']['s'.$sheeIndex] = array();
                    $data['row']['s'.$sheeIndex] = array();
                    //获取表头数据
                    $vcolumn = array();
                    $columnItem = '';
                    for(;$ic< $countClomun ; $ic++ ){
                        $cindex = $this->_index( $ic + 1).'1';
                        $columnItem = $currentSheet->getCell( $cindex )->getValue();
                        if( $columnItem instanceof \PHPExcel_RichText )
                            $columnItem = $currentSheet->getCell( $cindex )->getValue()->getPlainText();
                        if( $columnItem )
                            $vcolumn[] = $columnItem;
                        else
                            break;
                    }
                    $data['column']['s'.$sheeIndex]  = $vcolumn;

                    //获取列表数据
                    for($rowIndex = 0; $rowIndex< $allRow ; $rowIndex ++ ){
                        $vitem = array();
                        for($ic=0; $ic<$countClomun; $ic++ ){
                            $rowItem = false;
                            $cindex = $this->_index( $ic +1 );
                            $cindex .= ($rowIndex+2);
                            $cell = $currentSheet->getCell( $cindex );

                            if( !$currentSheet->getCell( $cindex )->isFormula( $cell ) )
                                $rowItem = $cell->getValue();
                            else{//Excel公式处理
                                $rowItem = $cell->getOldCalculatedValue();
                                if($rowItem instanceof \PHPExcel_RichText)
                                    $rowItem = $cell->__toString();
                            }
                            //判断是否是Excel时间格式
                            if( \PHPExcel_Shared_Date::isDateTime( $cell ) ){
                                 $tm =  gmdate("Y-m-d H:i:s" , \PHPExcel_Shared_Date::ExcelToPHP( $rowItem ));
                                 if ($tm&&date('Y', strtotime($tm))>1970  ){
                                     $rowItem = $tm;
                                 }
                                 else{
                                     if (strtotime($rowItem) && date('Y', strtotime($rowItem))>1970 )
                                        $rowItem = date('Y-m-d H:i:s', strtotime($rowItem) );
                                 }
                            }
                            else
                                $rowItem = trim( $rowItem );
                            $vitem[] = $rowItem;
                        }
                        $constr = implode('', $vitem );
                        if( trim( $constr) != '' )
                            $data['row']['s'.$sheeIndex][] = $vitem;
                        else
                            break ;
                    }
                }
                $res['rcode'] = 1;
                $res['msg'] = 'OK';
                $res['data'] = $data;
            }
            catch( \Exception $e){
                $res['msg'] = "上传Excel解析失败,错误消息：".$e->getMessage();
            }

        }
        return $res;
    }

    /**
     * 添加Excel sheet数据
     * @param Sheet $sheet 新添加的sheet数据
     * @return  返回当前对象指针
     */
    public function add_sheet( $sheet )
    {
        if( $sheet instanceof Sheet ){
            array_push( $this->m_sheet_data , $sheet );
            if( false === $this->m_sheet_active  )
                $this->m_sheet_active = 0;
            else
               $this->m_sheet_active ++;
           $this->m_sheet_error = "";
        }
        else{
            $this->m_sheet_error = "插入Sheet数据不正确";
        }
        return $this;
    }

    public function last_sheet_error()
    {
        return $this->m_sheet_error;
    }

    /**
     * 更新当前sheet对象
     * @param  Sheet $sheet 更新数据
     * @return  返回当前对象指针
     */
    public function update_sheet( $sheet )
    {
        return $this;
    }

    /**
     * [select_sheet description]
     * @param  index $index  设置当前活动Sheet
     * @return 返回当前对象指针
     */
    public function select_sheet( $index )
    {
        return $this;
    }

    /**
     * 导出存储的Excel数据
     *
     * @return array array(
     *                     'rcode'=>0/1,
     *                     'msg'  => '',
     *                     'path' => ''Excel存储路径
     *                 )
     */
    public function export_sheet( $title , $file_name ='')
    {
        $res = $this->m_res;
        if( count( $this->m_sheet_data ) == 0){
            $res['msg'] = "表格数据为空";
        }
        else{
            Vendor('PHPExcel.PHPExcel');
            $objPHPExcel = new \PHPExcel();
            $objProps = $objPHPExcel->getProperties()
                                    ->setCreator( "hzcfcenter.com")
                                    ->setTitle(  $title  )
                                    ->setSubject( $title );
            for( $isheet = 0; $isheet< count( $this->m_sheet_data ); $isheet ++ ){
               $objSheet = $isheet==0? $objPHPExcel->getActiveSheet() : $objPHPExcel->createSheet($isheet);
               $objPHPExcel->setActiveSheetIndex( $isheet );
               $objSheet->setTitle( $this->m_sheet_data[$isheet]->getName() );
               $dColumn = $this->m_sheet_data[$isheet]->getColumn();
               $dRows = $this->m_sheet_data[$isheet]->getRows();
               //表头设置
               $countC = count( $dColumn );
               for( $i=0; $i< $countC; $i ++ ){
                    $cindex = $this->_index( $i+1);
                    $cellValue = $this->_cell( $dColumn[$i] );
                    $objSheet->setCellValue( $cindex.'1', $cellValue );
               }
               //表数据
               $countR = count( $dRows );
               for( $irow = 0; $irow < $countR; $irow ++ ){
                    $countC = count( $dRows[$irow] );
                    for( $i=0; $i< $countC; $i ++ ){
                        $cindex = $this->_index( $i+1).($irow+2);
                        $cellValue = $this->_cell( $dRows[$irow][$i] );
                        $objSheet->setCellValue( $cindex, $cellValue );
                   }
               }
            }
            $MAtt = new AttUpload();
            $root_path = $MAtt->_root( false );
            $cfg_excel = C('EXCEL');
            //不要注释，相对路径在有时候会报错。
            /*   Excel导出文件格式
            *    BIFF Format
            *    Used by Microsoft Excel between versions 95 and 2003 File
            *    extension: xls
            *    PHPEXcel Writer: PHPExcel_Writer_Excel5
            *    Mime Type: application/vnd.ms-excel
            *    OfficeOpenXML Format
            *
            *    Used by Microsoft Excel since version 2007
            *    File extension: xlsx
            *    PHPEXcel Writer: PHPExcel_Writer_Excel2007
            *    Mime Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            */
            $new_path = $root_path . $cfg_excel['DIR'];
            if ($file_name)
                $new_path .= $file_name;
            else{
                $file_name = uniqid().'.xls';
                $new_path .= date( $cfg_excel['DIR_RULE'] ).DIRECTORY_SEPARATOR.$file_name;
            }
            $new_path = str_replace('//', '/', $new_path );
            $base_name = dirname($new_path);
            if( !is_dir( $base_name ))
                mkdir( $base_name, 0755, true );
            if( !is_dir( $base_name )){
                $res['msg'] = "Excel文件夹创建失败";
            }
            else{
                try{
                    //Excel2007,  生成2007excel格式的xlsx文件
                    //Excel5,生成2003excel格式的xls文件
                    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                    ob_end_clean(); // Added by me
                    ob_start(); // Added by me
                    if (file_exists($new_path))
                        unlink($new_path);
                    $objWriter->save( $new_path );
                    $res['rcode'] =1;
                    $res['msg'] = 'OK';
                    $res['data'] = $MAtt->Finfo( $new_path );
                }catch(Exception $e){
                    $res['msg'] = "Excel文件保存失败";
                }
            }

        }

        return $res;
    }

    private function _cell( $v )
    {
        if( $v && strpos($v,'=') === 0 ){
                $v = "'".$v;
        }
        return $v;
    }
    /**
     * Excel表格列表 index 字母与数字相互转换
     * @param  string $_v [description]
     * @return [type]     [description]
     */
    private function _index( $_v = '')
    {
        if( !$_v )
            return false;
        // 表头从1开始
        if( preg_match('#^[\d]+$#isU', $_v ) || is_int( $_v ) ){
            $str = '';
            $v = intval( $_v );
            if ($v<27){
                $str = chr( 64 + $v );
            }
            else if($v>26 && $v< 702){
                $str = chr(64 + ($v / 26)) . chr(65 + $v%26);  
            }
           else {
               $str = chr(64 + (($v - 26) / 676)) . chr(65 + ((($v - 26) % 676) / 26)) . chr(65 + $v % 26);  
           }
            return  $str;
        }
        else{
            $_v = strtoupper( $_v );
            $len = strlen( $_v );
            $v = 0;
            for( $i=0;$i< strlen( $_v ); $i ++ ){
                $len--;
                $p = $len > 0 ? pow( 26, $len ) : 1;
                $v += ( ord($_v[$i]) - 64 ) * $p ;
            }
            return $v;
        }
    }

    private function valid( $path )
    {
        if( !$path )
            return false;
        if( !file_exists( $path ) ) return false;
        $ext = basename( $path );
        $ext = substr( $path , strrpos($path, ".")+1);
        $ext = strtolower( $ext );
        if( $ext != 'xls' && $ext != 'xlsx' )
            return false;
        return true;
    }
}

/**
 * Excel sheet 单独数据样式
 *
 */
class Sheet
{
    protected $m_name = "";
    protected $m_column = array();
    protected $m_rows = array();
    static public $instance_count = 0;

    public function __construct( $_name = "" )
    {
        self::$instance_count+=1;
        if( $_name )
            $this->m_name = $_name;
        else
            $this->m_name =  "Sheet".self::$instance_count;
    }

    public function setName ( $_name )
    {
        $this->m_name = $_name;
    }

    public function getName ( )
    {
        return $this->m_name;
    }

    public function setColumn( $_data )
    {
        $this->m_column = $_data;
    }

    public function setRow( $_data )
    {
        $this->m_rows = $_data;
    }

    public function addRow( $_data )
    {
        $this->m_rows[] = $_data;
        return $this;
    }

    public function getColumn( )
    {
        return $this->m_column;
    }

    public function getRows()
    {
        return $this->m_rows;
    }
}

?>
